IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].GetInsightRegisteredUsers') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].GetInsightRegisteredUsers
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.GetInsightRegisteredUsers 
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @primayEmailType INT; SET @primayEmailType = 1
	DECLARE @activeUserBit INT;	SET @activeUserBit = 4

	SELECT	adp.FwkDomainUserId as Id, 
		(ltrim(rtrim(case patindex('%,%', adp.LastName) when 0 then adp.LastName else substring(adp.LastName, 0, patindex('%,%', adp.LastName)) end)) + ', ' +
		ltrim(rtrim(case patindex('%,%', adp.FirstName) when 0 then adp.FirstName else substring(adp.FirstName, 0, patindex('%,%', adp.FirstName)) end)) +
		case when len(ltrim(rtrim(isnull(adp.MiddleName, '')))) = 0 then '' else ' ' + ltrim(rtrim(adp.MiddleName)) end) as PIName,
		ltrim(rtrim(isnull(aea.EmailAddress, ''))) as EMail
		FROM	AdmPerson adp
	INNER JOIN FwkDomainUser du
		ON adp.FwkDomainUserId = du.Id
	INNER JOIN AdmInternalPerson ip
		ON adp.Id = ip.AdmPersonId 		
	INNER JOIN FwkApplicationUser au
		ON du.Id = au.FwkDomainUserId
	LEFT JOIN AdmExternalPerson aep
		ON aep.Id = ip.AdmPersonId
	LEFT OUTER JOIN AdmEmailAddress aea 
	on aea.AdmPersonId = adp.Id
	and aea.AdmEmailAddressTypeId = @primayEmailType
	WHERE (du.Username != '' or du.Username != null)
	and au.IsActiveBitmask = @activeUserBit
	group by adp.FwkDomainUserId, adp.LastName, adp.FirstName , adp.MiddleName, aea.EmailAddress
	order by adp.LastName, adp.FirstName

END
GO
